﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

public partial class User_insert : System.Web.UI.Page
{
    static int qa = 0, qb = 0, qc = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        //判断是否有要显示的值的传递
        
        
        if (!IsPostBack)
        {
            string str = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            //部门下拉菜单
            string cmdtext = "select * from tb_Department";
            SqlCommand cmd = new SqlCommand(cmdtext,conn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            DropDownListDepartment.DataSource = dt;
            DropDownListDepartment.DataTextField = "Department";
            DropDownListDepartment.DataValueField = "ID";
            DropDownListDepartment.DataBind();

            //职务下拉菜单
            string cmdtext1 = "select * from tb_Position";
            SqlCommand cmd1 = new SqlCommand(cmdtext1, conn);
            adapter = new SqlDataAdapter(cmd1);
            dt = new DataTable();
            adapter.Fill(dt);
            DropDownListPosition.DataSource = dt;
            DropDownListPosition.DataTextField = "Position";
            DropDownListPosition.DataValueField = "ID";
            DropDownListPosition.DataBind();

            //权限下拉菜单
            string cmdtextpermissions = "select * from tb_Permissions";
            SqlCommand cmdpermissions = new SqlCommand(cmdtextpermissions, conn);
            adapter = new SqlDataAdapter(cmdpermissions);
            dt = new DataTable();
            adapter.Fill(dt);
            dt.Rows.RemoveAt(0);
            DropDownListPermissions.DataSource = dt;
            DropDownListPermissions.DataTextField = "Permissions";
            DropDownListPermissions.DataValueField = "ID";
            DropDownListPermissions.DataBind();


            //第一学历下拉菜单
            string cmdtext2 = "select * from tb_Degree";
            SqlCommand cmd2 = new SqlCommand(cmdtext2, conn);
            adapter = new SqlDataAdapter(cmd2);
            dt = new DataTable();
            adapter.Fill(dt);
            DropDownListFirstDegree.DataSource = dt;
            DropDownListFirstDegree.DataTextField = "Degree";
            DropDownListFirstDegree.DataValueField = "ID";
            DropDownListFirstDegree.DataBind();


            //第二学历下拉菜单
            string cmdtext3 = "select * from tb_Degree";
            SqlCommand cmd3 = new SqlCommand(cmdtext3, conn);
            adapter = new SqlDataAdapter(cmd3);
            dt = new DataTable();
            adapter.Fill(dt);
            DropDownListSecondDegree.DataSource = dt;
            DropDownListSecondDegree.DataTextField = "Degree";
            DropDownListSecondDegree.DataValueField = "ID";
            DropDownListSecondDegree.DataBind();

            conn.Close();
        }
    }

   
    protected void TextBoxUserName_TextChanged(object sender, EventArgs e)
    {
        string str = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
        SqlConnection conn = new SqlConnection(str);
        conn.Open();
        //查询是否存在用户名
        string selectUser = "select UserName from tb_User where UserName ='"+ TextBoxUserName.Text +"'";
        SqlCommand comm = new SqlCommand(selectUser, conn);

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = comm;

        DataSet ds = new DataSet();
        da.Fill(ds,"User");

        if (ds.Tables["User"].Rows.Count > 0)
        {
            Labelyz.Text = "用户名已存在!";
            qa = 1;
        }
        else
        {
            Labelyz.Text = "此用户名可用!";
            qa = 0;
        }
        conn.Close();
    }
    protected void TextBoxIDCard_TextChanged(object sender, EventArgs e)
    {

        string str = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
        SqlConnection conn = new SqlConnection(str);
        conn.Open();
        string IDCard = TextBoxIDCard.Text;
           
            
        
        
           
        string SelectIDCard = "select State from tb_EmployeeInfo where IDCard ='" + IDCard + "'";
            
        SqlCommand comm = new SqlCommand(SelectIDCard, conn);


            
        SqlDataAdapter da = new SqlDataAdapter();
            
        da.SelectCommand = comm;

            
        DataSet ds = new DataSet();
            
        da.Fill(ds, "state");
            
        if (IDCard.Length != 18)
            
        {
               
            LableID.Text = "此身份证号非18位！";
            
        }
            
        else
            
        {
                
            LableID.Text = "此身份证号可用！";
            
        }

            
        if (ds.Tables["state"].Rows.Count > 0)
                if (ds.Tables["state"].Rows[0][0].ToString() == "1")
                {
                    LableID.Text = "此身份证号为在职员工！";
                    qb = 1;
                }
                else
                    if (ds.Tables["state"].Rows[0][0].ToString() == "3")
                    {
                        LableID.Text = "此身份证号为黑名单员工！";
                        qb = 1;
                    }
                    else
                    {
                        //比较员工离职与现在时间差
                        string Time = "selcet DelTime from tb_EmployeeInfo where IDCard '" + IDCard + "'";
                        SqlCommand commTime = new SqlCommand(Time, conn);
                        SqlDataAdapter GTime = new SqlDataAdapter();
                        GTime.SelectCommand = commTime;

                        DataSet GetTime = new DataSet();
                        GTime.Fill(GetTime, "time");
                        string DelTime = GetTime.Tables["time"].Rows[0][0].ToString();
                        DateTime timelz =Convert.ToDateTime(DelTime);
                        TimeSpan time2 = DateTime.Now.Subtract(timelz);
                        if (time2.Days < 365)
                        {
                            LableID.Text = "此身份证号为正常离职未达一年员工！";
                            qb = 1;
                        }
                        else
                        {
                            string delete = "delete from tb_EmployeeInfo where IDCard ='"+ IDCard +"'";
                            SqlCommand commdel = new SqlCommand(delete,conn);
                            commdel.ExecuteNonQuery();
                            qb = 0;
                        }
                        
                    }
            conn.Close();
      

    }
    //保存控件
    protected void ButtonSave_Click(object sender, EventArgs e)
    {
        string connString = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
        SqlConnection conn = new SqlConnection(connString);
        try
        {
            if (qa + qb + qc > 0)
            {
                Response.Write("<script>alert('保存失败');</script>");
                return;
            }
            string fileName = "";
            if (FileUpload.HasFile)
            {

                //获得文件名
                fileName = FileUpload.FileName;
                //获取文件在服务器中存放的位置
                string filePath = Server.MapPath("~/Pic/");
                //设置时间刻度
                string fileTimeName = DateTime.Now.Ticks.ToString();
                //以时间刻度定义文件名
                string fileAlName = fileTimeName + fileName;
                fileName = fileAlName;
                FileUpload.PostedFile.SaveAs(filePath + fileAlName);
            }

            conn.Open();
            string cmdInsertEmployeeinfo = "insert into tb_Employeeinfo(Department,Position,Name,sex,BirthdayYang," +
                "BloodType,Nation,Origin,Political,GraduationTime,GraduationSchool,Edu1,Edu1Professional,Edu1Degree,Edu2,"
            + "Edu2Professional,Edu2Degree,ForLanLev,ComLev,IDCard,PerReside,Marrige,Tittle,Health,LoverName,LoverBir,"
            + "ChiName,ChiBir,WorkExper,Address,OtherAddress,Phone,TelePhone,Email,QQ,EmerPer,EmerPhone,WorkTime,PostTime,"
            + "Rec,RecName,Fam,FamName,FamPosition,Experience,Honors,SocialRelation,InAndHo,SelfAsse,Pic,Danwei,Wage" +
            ") values ('" + DropDownListDepartment.SelectedValue + "','" + DropDownListPosition.SelectedValue +
            "','" + TextBoxName.Text + "','" + RadioButtonSex.SelectedValue + "','" + TextBoxBirthday.Text + "','" + DropDownListBloodType.SelectedValue +
            "','" + TextBoxNation.Text + "','" + TextBoxOrigin.Text +
            "','" + TextBoxPolitical.Text + "','" + TextBoxGraduationTime.Text + "','" + TextBoxGraduationSchool.Text + "','" + TextBoxEdu1.Text +
            "','" + TextBoxProfessional.Text + "','" + DropDownListFirstDegree.SelectedValue + "','" + TextBoxEdu2.Text + "','" + TextBoxEdu2Professional.Text +
            "','" + DropDownListSecondDegree.SelectedValue + "','" + TextBoxForLanLev.Text + "','" + TextBoxComLev.Text + "','" + TextBoxIDCard.Text +
            "','" + TextBoxPerReside.Text + "','" + TextBoxMarrige.Text + "','" + TextBoxTittle.Text + "','" + TextBoxHealth.Text + "','" + TextBoxLoverName.Text
            + "','" + TextBoxLoverBir.Text + "','" + TextBoxChiName.Text + "','" + TextBoxChiBir.Text + "','" + TextBoxWorkExper.Text + "','" + TextBoxAddress.Text +
            "','" + TextBoxOtherAddress.Text + "','" + TextBoxPhone.Text + "','" + TextBoxTelePhone.Text + "','" + TextBoxEmail.Text + "','" + TextBoxQQ.Text +
            "','" + TextBoxEmerPer.Text + "','" + TextBoxEmerPhone.Text + "','" + TextBoxWorkTime.Text + "','" + TextBoxPostTime.Text + "','" + TextBoxRec.Text +
            "','" + TextBoxRecName.Text + "','" + TextBoxFam.Text + "','" + TextBoxFamName.Text + "','" + TextBoxFamPosition.Text + "','" + TextBoxExperience.Text +
            "','" + TextBoxHonors.Text + "','" + TextBoxSocialRelation.Text + "','" + TextBoxInAndHo.Text + "','" + TextBoxSelfAsse.Text + "','" + fileName +
            "','" + TextBoxDanwei.Text + "','" + TextBoxgongzi.Text + "');";
            cmdInsertEmployeeinfo += "SELECT CAST(scope_identity() AS int)";


            SqlCommand cmd = new SqlCommand(cmdInsertEmployeeinfo, conn);
            int eid = (int)cmd.ExecuteScalar();

            string cmdInsertUser = "insert into tb_User(UserName,UserPwd,EID,Flag) values ('" + TextBoxUserName.Text + "','" + TextBoxUserPwd.Text +
            "','" + eid + "','"+DropDownListPermissions.SelectedValue+"')";
            SqlCommand cmdUser = new SqlCommand(cmdInsertUser, conn);
            cmdUser.ExecuteNonQuery();



            conn.Close();
            //Response.Write("<script>return confirm('请确认是否删除？');</script>");
            Response.Write("<script>alert('添加成功!');window.location.href ='EManage.aspx'</script>");
            //Response.Redirect("EManage.aspx");
        }
        catch (Exception ex)
        {
            conn.Close();
        }

    }

    protected void ButtonBack_Click(object sender, EventArgs e)
    {
        Response.Redirect("EManage.aspx");
    }
}